package SpgBI_Dec.Biz;

import javax.xml.transform.OutputKeys;
import javax.xml.transform.Result;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import java.io.*;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import SpgBI_Dec.Common.Arith;
import SpgBI_Dec.Common.DBUtils;
import SpgBI_Dec.Common.Encode;
import SpgBI_Dec.Test;
import org.dom4j.*;
import org.dom4j.io.DocumentSource;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import org.jdom2.Element;
import org.jdom2.input.SAXBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.slf4j.MDC;


public class Voucher {

    private static Logger _log = LoggerFactory.getLogger(Voucher.class);

    public static void ExchangeAllData(){
        Connection connERP = null;
        PreparedStatement pstmtVoucherlist = null;
        ResultSet rsVoucherlist = null;
        try {
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherlist = new StringBuffer();
            strSqlVoucherlist.append("select samevoucher,pk_corp,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,\n");
            strSqlVoucherlist.append("sum(d) totalcredit,sum(j) totaldebit,ncuser,NVL(ISREAD,'U') ISREAD,NVL(ISSUCCESS,'U') ISSUCCESS,REMARK from CW_XZD_YSPZ_EXCHANGE where \n");
            strSqlVoucherlist.append("((ISREAD <> 'N' OR ISREAD IS NULL) AND issuccess IS NULL)\n");
            strSqlVoucherlist.append("group by samevoucher,pk_corp,rq,ncuser,ISREAD,ISSUCCESS,REMARK \n");
            strSqlVoucherlist.append("ORDER BY pk_corp,to_char(rq,'YYYY-MM-DD')");
            pstmtVoucherlist = connERP.prepareStatement(strSqlVoucherlist.toString());
            rsVoucherlist = pstmtVoucherlist.executeQuery();
            while (rsVoucherlist.next()){
                    ExchangeData(rsVoucherlist.getString("samevoucher"));
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            DBUtils.close(connERP);
        }

    }

    public static void ExchangeData(String VoucherID){
        MDC.put("VOUCHERID",VoucherID);
        Connection connERP = null;
        PreparedStatement pstmtVoucherBody = null;
        ResultSet rsVoucherBody = null;
        List lstVoucherBody = null;
        StringBuffer Msg = null;
        String retStatus = "Y";
        int i;
        Map<String,String> retMap= null;
        SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
        try {
            Msg = new StringBuffer("开始进行批次号为" + VoucherID + "的财务数据交换任务,");
            Msg.append("时间：");
            Msg.append(fmt.format(new Date()));
            Msg.append("\n");
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherBody = new StringBuffer();
            strSqlVoucherBody.append("SELECT ROWNUM as FLOWID,T.* FROM (");
            strSqlVoucherBody.append("SELECT ID,SAMEVOUCHER,PK_CORP,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,");
            strSqlVoucherBody.append("NCUSER,NVL(ISREAD,'U') ISREAD,NVL(ISSUCCESS,'U') ISSUCCESS,");
            strSqlVoucherBody.append("zy,kmdm,fzbz,dwdm,DQH,CUSTNAME,NCFLAG,jtnwfz,'CNY' bz,j debitamount,d creditamount,REMARK ");
            strSqlVoucherBody.append("FROM CW_XZD_YSPZ_EXCHANGE WHERE SAMEVOUCHER = ? ");
            strSqlVoucherBody.append("ORDER BY DECODE(J,0,2,1),KMDM,DWDM");
            strSqlVoucherBody.append(") T");
            pstmtVoucherBody = connERP.prepareStatement(strSqlVoucherBody.toString());
            pstmtVoucherBody.setString(1, VoucherID);
            rsVoucherBody = pstmtVoucherBody.executeQuery();
            lstVoucherBody = DBUtils.ExtractData(rsVoucherBody);
            retStatus = ((Map)lstVoucherBody.get(0)).get("ISREAD").toString();
            if (!retStatus.equals("Y")){

                    retMap = VerifyVoucher(VoucherID,lstVoucherBody);
                    if (retMap != null){
                        retStatus = retMap.get("RESULTS");
                        Msg.append(retMap.get("MESSAGE"));
                    }
                    else
                        retStatus = "U";
                pstmtVoucherBody = connERP.prepareStatement("UPDATE CW_XZD_YSPZ_EXCHANGE SET ISREAD = ? WHERE SAMEVOUCHER = ?");
                switch (retStatus){
                    case "Y":
                        Msg.append("凭证校验通过\n------------------------\n");
                        pstmtVoucherBody.setString(1,"Y");
                        pstmtVoucherBody.setString(2, ((Map) lstVoucherBody.get(0)).get("SAMEVOUCHER").toString());
                        pstmtVoucherBody.execute();
                        break;
                    case "N":
                        Msg.append("凭证校验没有通过，结束本次数据交换\n------------------------\n");
                        pstmtVoucherBody.setString(1,"N");
                        pstmtVoucherBody.setString(2, ((Map) lstVoucherBody.get(0)).get("SAMEVOUCHER").toString());
                        pstmtVoucherBody.execute();
                        return;
                    case "U":
                        Msg.append("凭证传输时出现异常，本次数据交换终止。\n----------------------------\n");
                        return;
                }

            }
            else
            {
                Msg.append("凭证已通过校验工作，不再重复校验\n");
            }


            //如果不存在，则新建客商档案
            for (i = 0; i < lstVoucherBody.size(); i++) {
                if (((Map) lstVoucherBody.get(i)).get("DWDM") != null && ((Map) lstVoucherBody.get(i)).get("CUSTNAME") != null) {
                    if (((Map) lstVoucherBody.get(i)).get("NCFLAG") == null || ((Map) lstVoucherBody.get(i)).get("NCFLAG").toString() == "") {
                        retMap = Cubas.ExchangeCubasData(VoucherID,((Map) lstVoucherBody.get(i)).get("PK_CORP").toString(), ((Map) lstVoucherBody.get(i)).get("DWDM").toString(), ((Map) lstVoucherBody.get(i)).get("CUSTNAME").toString());
                        if (retMap != null){
                            retStatus = retMap.get("RESULTS");
                            Msg.append(retMap.get("MESSAGE"));
                        }
                        else {
                            retStatus = "U";
                        }
                        if (retStatus.equals("Y")) {
                            pstmtVoucherBody = connERP.prepareStatement("UPDATE CW_XZD_YSPZ_EXCHANGE SET NCFLAG = ? WHERE ID = ?");
                            pstmtVoucherBody.setString(1, ((Map) lstVoucherBody.get(i)).get("DWDM").toString());
                            pstmtVoucherBody.setString(2, ((Map) lstVoucherBody.get(i)).get("ID").toString());
                            pstmtVoucherBody.execute();
                            ((Map)lstVoucherBody.get(i)).put("NCFLAG",((Map)lstVoucherBody.get(i)).get("DWDM").toString());
                        }
                        else {
                            Msg.append("第" + String.valueOf(i+1) + "条分录中的客商,");
                            Msg.append("客商代码为："+((Map) lstVoucherBody.get(i)).get("DWDM").toString());
                            Msg.append(",客商名称为：" + ((Map) lstVoucherBody.get(i)).get("CUSTNAME").toString());
                            Msg.append(",没有传输成功，结束本次数据交换\n");
                            return;
                        }
                    }
                }
            }
            Msg.append("完成分录中的辅助信息的新增\n-----------------\n");
            pstmtVoucherBody = connERP.prepareStatement("UPDATE CW_XZD_YSPZ_EXCHANGE SET ISSUCCESS = ? WHERE SAMEVOUCHER = ?");
            retMap = ExchangeVoucherFile(VoucherID,CreateVoucherDocument(VoucherID,lstVoucherBody));
            if (retMap != null){
                retStatus = retMap.get("RESULTS");
                Msg.append(retMap.get("MESSAGE"));
            }
            else {
                retStatus = "U";
            }
            switch (retStatus){
                case "Y":
                    pstmtVoucherBody.setString(1, "Y");
                    pstmtVoucherBody.setString(2, ((Map) lstVoucherBody.get(0)).get("SAMEVOUCHER").toString());
                    pstmtVoucherBody.execute();
                    Msg.append("凭证传输成功，本次数据交换完成。\n----------------------------\n");
                    break;
                case "N":
                    pstmtVoucherBody.setString(1, "N");
                    pstmtVoucherBody.setString(2, ((Map) lstVoucherBody.get(0)).get("SAMEVOUCHER").toString());
                    pstmtVoucherBody.execute();
                    Msg.append("凭证传输失败，本次数据交换结束。\n----------------------------\n");
                    break;
                case "U":
                    Msg.append("凭证传输时出现异常，本次数据交换终止。\n----------------------------\n");
                    break;
            }

        }catch (Exception ex){
            ex.printStackTrace();
            _log.error(ex.getStackTrace().toString());
            Msg.append("凭证传输时出现异常，结束本次数据交换\n");
            retStatus =  "U";
        }
        finally {
            if (lstVoucherBody != null) {
                lstVoucherBody = null;
            }
            _log.info(Msg.toString());
            DBUtils.close(connERP);
        }

    }

    public static Map<String,String> ExchangeVoucherFile(String VoucherID,String VoucherFile) {

        String url = "http://172.22.6.7:80/service/XChangeServlet?account=02";
        File file = null;
        StringBuffer Msg = new StringBuffer();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
        String resSuc = "U";
        Map<String,String> retMap= null;
        try {
            retMap = new HashMap<>();
            if ("".equals(VoucherFile)){
                Msg.append("创建凭证交换文档时出错，具体信息请查看日志。");
                resSuc = "U";
                return retMap;
            }else {
                file = new File(VoucherFile);
            }
            Msg.append("开始向nc传输凭证单据,时间："+fmt.format(new Date())+"\n");

            URL realURL = new URL(url);
            HttpURLConnection connection = (HttpURLConnection) realURL.openConnection();
            connection.setDoOutput(true);
            connection.setRequestProperty("Content-type", "text/xml");
            connection.setRequestMethod("POST");

            BufferedOutputStream out = new BufferedOutputStream(connection.getOutputStream());
            BufferedInputStream input = new BufferedInputStream(new FileInputStream(file));
            int length;
            byte[] buffer = new byte[1000];
            while ((length = input.read(buffer, 0, 1000)) != -1) {
                out.write(buffer, 0, length);
            }
            input.close();
            out.close();


            // 从连接的输入流中取得回执信息
            /***************从输入流取得Doc***************/
            InputStream inputStream = connection.getInputStream();

            InputStreamReader isr = new InputStreamReader(inputStream);
            BufferedReader bufreader = new BufferedReader(isr);
            String xmlString = "";
            int c;
            //System.out.println("==================Beging====================");
            while ((c = bufreader.read()) != -1) {
                //System.out.print((char) c);
                xmlString += (char) c;
            }
            input.close();
            //System.out.println("===================End======================");
            Document resDoc = DocumentHelper.parseText(xmlString);
            Msg.append("等待NC返回传输结果\n");

            // 对回执结果的后续处理
            /************document转化为xml*************/
            TransformerFactory tFactory = TransformerFactory.newInstance();
            Transformer transformer = tFactory.newTransformer();
            DocumentSource source = new DocumentSource(resDoc);
            transformer.setOutputProperty(OutputKeys.ENCODING, "GB2312");
            //设置文档的换行与缩进
            transformer.setOutputProperty(OutputKeys.INDENT, "YES");


            //设置日期格式

            String resFile = "D:\\data\\bkmsg\\BkMsg__" + fmt.format(new Date()) + ".xml";
            StreamResult result = new StreamResult(new File(resFile));
            transformer.transform(source, result);
            //System.out.println("======生成回执文件成功=======");
            Msg.append("已取得NC返回传输结果，解析结果如下：\n");


            /**************jdom解析XML*****************/
            SAXBuilder saxReader = new SAXBuilder();
            org.jdom2.Document document1 = saxReader.build(new File(resFile));
            org.jdom2.Element root = document1.getRootElement();
            //获取根元素,得到导入用友是否成功successful的值,值为Y:成功 N:失败
            resSuc = root.getAttributeValue("successful");
            Msg.append("单据导入");
            Msg.append("Y".equals(resSuc) ?"成功":"失败");
            Msg.append(",信息如下：\n");

            //后面对回执结果做判断,然后改变导入状态就行了
            if (null != resSuc) {
                if (resSuc.equals("N")) {
                    Msg.append("导入失败,信息如下：\n");
                } else if (resSuc.equals("Y")) {
                    Msg.append("导入成功,,信息如下：\n");
                } else {
                    Msg.append("返回异常状态,信息如下：\n");
                    resSuc = "U";
                }
            } else {
                Msg.append("返回异常状态,,信息如下：\n");
                resSuc = "U";
            }

            List<Element> list = root.getChildren();
            for (org.jdom2.Element e : list) {
                /*
                System.out.println("-------------------------");
                System.out.println("filename---> " + e.getChildText("filename"));
                System.out.println("resultcode---> " + e.getChildText("resultcode"));
                System.out.println("resultdescription---> " + e.getChildText("resultdescription"));
                System.out.println("content--> " + e.getChildText("content"));
                System.out.println("--------------------------------------");
                */
                Msg.append(e.getChildText("resultdescription"));
                Msg.append("\n");
                Msg.append("凭证生成的时间与凭证号为：");
                Msg.append(e.getChildText("content"));
                Msg.append("\n");
            }


            //file.delete();

        } catch (Exception ex)
        {
            ex.printStackTrace();
            //_log.error(ex.getStackTrace().toString());
            Msg.append("出现异常状态,请查看日志\n");
            resSuc = "U";
            return retMap;
        }
        finally {
            retMap.put("RESULTS",resSuc);
            retMap.put("MESSAGE",Msg.toString());
            Msg = null;
            return  retMap;
        }
    }

    public static String CreateVoucherDocument(String VoucherID) throws Exception {
        Connection connERP = null;
        PreparedStatement pstmtVoucherbody = null;
        ResultSet rsVoucherBody = null;
        String ErrMsg = "";
        XMLWriter writer = null;
        String resFile = "";
        try {
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherBody = new StringBuffer();
            strSqlVoucherBody.append("SELECT ROWNUM as FLOWID,T.* FROM (");
            strSqlVoucherBody.append("SELECT SAMEVOUCHER,PK_CORP,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,");
            strSqlVoucherBody.append("NCUSER,NVL(ISREAD,'U') ISREAD,NVL(ISSUCCESS,'U') ISSUCCESS,wb, ");
            strSqlVoucherBody.append("zy,kmdm,fzbz,dwdm,jtnwfz,'CNY' bz,j debitamount,d creditamount,REMARK ");
            strSqlVoucherBody.append("FROM CW_XZD_YSPZ_EXCHANGE WHERE SAMEVOUCHER = ? ");
            strSqlVoucherBody.append("ORDER BY DECODE(J,0,2,1),KMDM,DWDM");
            strSqlVoucherBody.append(") T");
            pstmtVoucherbody = connERP.prepareStatement(strSqlVoucherBody.toString());
            pstmtVoucherbody.setString(1, VoucherID);
            rsVoucherBody = pstmtVoucherbody.executeQuery();

            Document Doc = DocumentHelper.createDocument();
            org.dom4j.Element elUfinterface = Doc.addElement("ufinterface");

            elUfinterface.addAttribute("roottag", "voucher");
            elUfinterface.addAttribute("billtype", "gl");
            elUfinterface.addAttribute("subtype", "");
            elUfinterface.addAttribute("replace", "N");
            elUfinterface.addAttribute("receiver", "010175");
            elUfinterface.addAttribute("sender", "0101");
            elUfinterface.addAttribute("isexchange", "Y");
            elUfinterface.addAttribute("filename", "voucher.xml");
            elUfinterface.addAttribute("proc", "add");
            elUfinterface.addAttribute("operation", "req");

            org.dom4j.Element elVoucher = elUfinterface.addElement("voucher");
            //需要设置好凭证ID
            elVoucher.addAttribute("id", "abc");

            org.dom4j.Element elVoucher_head = elVoucher.addElement("voucher_head");

            elVoucher_head.addAttribute("company", "010175");
            elVoucher_head.addAttribute("voucher_type", "记账");
            elVoucher_head.addAttribute("fiscal_year", "2016");
            elVoucher_head.addAttribute("accounting_period", "12");
            elVoucher_head.addAttribute("voucher_id", "010175");
            elVoucher_head.addAttribute("attachment_number", "1");
            elVoucher_head.addAttribute("date", "2016-12-29");
            elVoucher_head.addAttribute("enter", "010175");
            elVoucher_head.addAttribute("cashier", "010175");
            elVoucher_head.addAttribute("signature", "010175");
            elVoucher_head.addAttribute("checker", "010175");
            elVoucher_head.addAttribute("operator", "010175");
            elVoucher_head.addAttribute("posting_date", "010175");
            elVoucher_head.addAttribute("posting_person", "010175");
            elVoucher_head.addAttribute("revokeflag", "010175");
            elVoucher_head.addAttribute("voucherkind", "0");
            elVoucher_head.addAttribute("voucher_making_system", "总账");
            elVoucher_head.addAttribute("memo1", "");
            elVoucher_head.addAttribute("memo2", "");
            elVoucher_head.addAttribute("reserve1", "");
            elVoucher_head.addAttribute("reserve2", "");

            org.dom4j.Element elVoucher_body = elVoucher.addElement("voucher_body");
            for (int i = 0; i < 39; i++) {
                org.dom4j.Element elEntry = elVoucher_body.addElement("entry");
                elEntry.addElement("entry_id").setText(String.valueOf(i));
                elEntry.addElement("account_code").setText(String.valueOf(i));
                elEntry.addElement("abstract").setText(String.valueOf(i));
                elEntry.addElement("document_id").setText(String.valueOf(i));
                elEntry.addElement("document_date").setText(String.valueOf(i));
                elEntry.addElement("unit_price").setText(String.valueOf(i));
                elEntry.addElement("exchange_rate1").setText(String.valueOf(i));
                elEntry.addElement("exchange_rate2").setText(String.valueOf(i));
                elEntry.addElement("debit_quantity").setText(String.valueOf(i));
                elEntry.addElement("primary_debit_amount").setText(String.valueOf(i));
                elEntry.addElement("secondary_debit_amount").setText(String.valueOf(i));
                elEntry.addElement("natural_debit_currency").setText("");
                elEntry.addElement("credit_quantity").setText("");
                elEntry.addElement("primary_credit_amount").setText("");
                elEntry.addElement("secondary_credit_amount").setText("");
                elEntry.addElement("natural_credit_currency").setText("");
                elEntry.addElement("bill_type").setText("");
                elEntry.addElement("bill_id").setText("");
                elEntry.addElement("bill_date").setText("");

                org.dom4j.Element elAuxiliary_accounting = elEntry.addElement("auxiliary_accounting");
                org.dom4j.Element elAuxiliary_accountingItem = elAuxiliary_accounting.addElement("item");
                elAuxiliary_accountingItem.addAttribute("name", "客商辅助核算");
                elAuxiliary_accountingItem.setText("010166");


                elEntry.addElement("detail").setText("");
                elEntry.addElement("freeitem1").setText("");
                elEntry.addElement("freeitem30").setText("");


            }
            OutputFormat XML_FORMAT = new OutputFormat();
            // 设置换行 为false时输出的xml不分行
            XML_FORMAT.setNewlines(true);
            // 生成缩进
            XML_FORMAT.setIndent(true);
            // 指定使用tab键缩进
            XML_FORMAT.setIndent("  ");
            XML_FORMAT.setEncoding("gb2312");
            //XMLWriter writer = new XMLWriter(format);
            SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
            resFile = "data/voucher/voucher_" + fmt.format(new Date()) + ".xml";
            File file = new File(resFile);
            writer = new XMLWriter(new FileOutputStream(file), XML_FORMAT);
            //BufferedInputStream input = new BufferedInputStream(format);
            writer.write(Doc);
        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            if (writer != null) writer.close();
            DBUtils.close(connERP);
            //DBUtils.close(connFIN);
            return resFile;
        }

        // 注意这里要记得关闭XmlWriter


    }

    public static String CreateVoucherDocument(String VoucherID,List VoucherBody) throws Exception {

        String ErrMsg = "";
        XMLWriter writer = null;
        String resFile = "";
        try {
            //设置时间戳
            SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");

            Document Doc = DocumentHelper.createDocument();
            org.dom4j.Element elUfinterface = Doc.addElement("ufinterface");

            elUfinterface.addAttribute("roottag", "voucher");
            elUfinterface.addAttribute("billtype", "gl");
            elUfinterface.addAttribute("subtype", "");
            elUfinterface.addAttribute("replace", "N");
            elUfinterface.addAttribute("receiver", ((Map) VoucherBody.get(0)).get("PK_CORP").toString());
            elUfinterface.addAttribute("sender", "0101");
            elUfinterface.addAttribute("isexchange", "Y");
            elUfinterface.addAttribute("filename", "");
            elUfinterface.addAttribute("proc", "add");
            elUfinterface.addAttribute("operation", "req");

            org.dom4j.Element elVoucher = elUfinterface.addElement("voucher");
            //需要设置好凭证ID

            elVoucher.addAttribute("id", ((Map) VoucherBody.get(0)).get("PK_CORP").toString() + ((Map) VoucherBody.get(0)).get("SAMEVOUCHER").toString() + fmt.format(new Date()));

            org.dom4j.Element elVoucher_head = elVoucher.addElement("voucher_head");

            elVoucher_head.addElement("company").setText(((Map) VoucherBody.get(0)).get("PK_CORP").toString());
            elVoucher_head.addElement("voucher_type").setText("记账");
            elVoucher_head.addElement("fiscal_year").setText(((Map) VoucherBody.get(0)).get("FISCAL_YEAR").toString());
            elVoucher_head.addElement("accounting_period").setText(((Map) VoucherBody.get(0)).get("ACCOUNTING_PERIOD").toString());
            elVoucher_head.addElement("voucher_id").setText("");
            elVoucher_head.addElement("attachment_number").setText("1");
            elVoucher_head.addElement("date").setText(((Map) VoucherBody.get(0)).get("VOUCHERDATE").toString());
            elVoucher_head.addElement("enter").setText(((Map) VoucherBody.get(0)).get("NCUSER").toString());
            elVoucher_head.addElement("cashier").setText("");
            elVoucher_head.addElement("signature").setText("");
            elVoucher_head.addElement("checker").setText("");
            elVoucher_head.addElement("operator").setText("");
            elVoucher_head.addElement("posting_date").setText("");
            elVoucher_head.addElement("posting_person").setText("");
            elVoucher_head.addElement("revokeflag").setText("N");
            elVoucher_head.addElement("voucherkind").setText("0");
            elVoucher_head.addElement("voucher_making_system").setText("总账");
            elVoucher_head.addElement("memo1").setText("");
            elVoucher_head.addElement("memo2").setText("");
            elVoucher_head.addElement("reserve1").setText("");
            elVoucher_head.addElement("reserve2").setText("");

            org.dom4j.Element elVoucher_body = elVoucher.addElement("voucher_body");
            for (int i = 0; i < VoucherBody.size(); i++) {
                org.dom4j.Element elEntry = elVoucher_body.addElement("entry");
                elEntry.addElement("entry_id").setText(((Map) VoucherBody.get(i)).get("FLOWID").toString());
                elEntry.addElement("account_code").setText(((Map) VoucherBody.get(i)).get("KMDM").toString());
                elEntry.addElement("abstract").setText(((Map) VoucherBody.get(i)).get("ZY").toString());
                elEntry.addElement("settlement").setText("");
                elEntry.addElement("document_id").setText("");
                elEntry.addElement("document_date").setText("");
                elEntry.addElement("currency").setText("CNY");
                elEntry.addElement("unit_price").setText("");
                elEntry.addElement("exchange_rate1").setText("0");
                elEntry.addElement("exchange_rate2").setText("");
                elEntry.addElement("debit_quantity").setText("0");
                elEntry.addElement("primary_debit_amount").setText(((Map) VoucherBody.get(i)).get("DEBITAMOUNT").toString());
                elEntry.addElement("secondary_debit_amount").setText("");
                elEntry.addElement("natural_debit_currency").setText(((Map) VoucherBody.get(i)).get("DEBITAMOUNT").toString());
                elEntry.addElement("credit_quantity").setText("0");
                elEntry.addElement("primary_credit_amount").setText(((Map) VoucherBody.get(i)).get("CREDITAMOUNT").toString());
                elEntry.addElement("secondary_credit_amount").setText("");
                elEntry.addElement("natural_credit_currency").setText(((Map) VoucherBody.get(i)).get("CREDITAMOUNT").toString());
                elEntry.addElement("bill_type").setText("");
                elEntry.addElement("bill_id").setText("");
                elEntry.addElement("bill_date").setText("");
                if (((Map) VoucherBody.get(i)).get("JTNWFZ") != null || ((Map) VoucherBody.get(i)).get("FZBZ") != null){
                    org.dom4j.Element elAuxiliary_accounting = elEntry.addElement("auxiliary_accounting");
                    org.dom4j.Element elAuxiliary_accountingItem = elAuxiliary_accounting.addElement("item");
                    if (((Map) VoucherBody.get(i)).get("FZBZ") != null && "Y".equals(((Map) VoucherBody.get(i)).get("FZBZ").toString())){
                        if (((Map) VoucherBody.get(i)).get("NCFLAG") != null) {
                            elAuxiliary_accountingItem.addAttribute("name", "客商辅助核算");
                            elAuxiliary_accountingItem.setText(((Map) VoucherBody.get(i)).get("NCFLAG").toString());
                        }
                    }
                    if (((Map) VoucherBody.get(i)).get("JTNWFZ") != null){
                        elAuxiliary_accountingItem.addAttribute("name", "海峡集团合并范围辅助核算");
                        elAuxiliary_accountingItem.setText(((Map) VoucherBody.get(i)).get("JTNWFZ").toString());
                    }
                }




                //elEntry.addElement("detail").setText("");
                //elEntry.addElement("freeitem1").setText("");
                //elEntry.addElement("freeitem30").setText("");
                //elEntry.addElement("otheruserdata").setText("");

            }
            OutputFormat XML_FORMAT = new OutputFormat();
            // 设置换行 为false时输出的xml不分行
            XML_FORMAT.setNewlines(true);
            // 生成缩进
            XML_FORMAT.setIndent(true);
            // 指定使用tab键缩进
            XML_FORMAT.setIndent("  ");
            XML_FORMAT.setEncoding("gb2312");
            //XMLWriter writer = new XMLWriter(format);
            resFile = "d:/data/voucher/voucher_" + fmt.format(new Date()) + ".xml";
            File file = new File(resFile);
            writer = new XMLWriter(new FileOutputStream(file), XML_FORMAT);
            //BufferedInputStream input = new BufferedInputStream(format);
            writer.write(Doc);
        } catch (Exception ex) {
            ex.printStackTrace();
            resFile = "";
        } finally {
            // 注意这里要记得关闭XmlWriter
            if (writer != null) writer.close();
            return resFile;
        }
    }

    public static String GetVoucherList(String UnitList,String Period,String ExchangeStatus) {
        Connection connERP = null;
        PreparedStatement pstmtVoucherlist = null;
        ResultSet rsVoucherlist = null;
        String rsJson = "";
        try {
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherlist = new StringBuffer();
            strSqlVoucherlist.append("select samevoucher,pk_corp,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,\n");
            strSqlVoucherlist.append("sum(d) totalcredit,sum(j) totaldebit,ncuser,DECODE(ISREAD,'Y','通过','N','未通过','未校验') ISREAD,DECODE(ISSUCCESS,'Y','成功','N','失败','未交换')  ISSUCCESS,REMARK from CW_XZD_YSPZ_EXCHANGE where \n");
            /*
            switch (ExchangeStatus){
                case "1":
                    strSqlVoucherlist.append("(issuccess = 'Y') AND \n");
                    break;
                case "2":
                    strSqlVoucherlist.append("(issuccess ='N') AND \n");
                    break;
                case "3":
                case "0":
                        strSqlVoucherlist.append("(issuccess = 'Y' or issuccess ='N') AND \n");
                        break;
            }
            */

            strSqlVoucherlist.append("to_char(rq,'YYYY-MM') = ?");
            String[] lstUnit = UnitList.split(",");
            if (!"".equals(lstUnit[0])) {
                strSqlVoucherlist.append(" AND PK_CORP IN (");
                for (int i = 0; i < lstUnit.length; i++) {
                    strSqlVoucherlist.append("'" + lstUnit[i] + "'");
                    if (i != lstUnit.length - 1) {
                        strSqlVoucherlist.append(",");
                    }
                }
                strSqlVoucherlist.append(")");
            }
            //strSqlVoucherlist.append("and pk_corp in ('1002') \n");
            strSqlVoucherlist.append(" group by samevoucher,pk_corp,rq,ncuser,ISREAD,ISSUCCESS,REMARK \n");
            strSqlVoucherlist.append("ORDER BY pk_corp,to_char(rq,'YYYY-MM-DD')");
            //System.out.println(strSqlVoucherlist.toString());
            pstmtVoucherlist = connERP.prepareStatement(strSqlVoucherlist.toString());
            pstmtVoucherlist.setString(1,Period.substring(0,7));
            rsVoucherlist = pstmtVoucherlist.executeQuery();
            rsJson = DBUtils.extractJSONArray(rsVoucherlist).toString();
            //System.out.println(rsJson);
        } catch (Exception ex) {
            ex.printStackTrace();
            rsJson = "失败";
        } finally {
            DBUtils.close(connERP);
            return rsJson;
        }


    }

    public static String GetVoucherBody(String VoucherId) {
        Connection connERP = null;
        PreparedStatement pstmtVoucherbody = null;
        ResultSet rsVoucherBody = null;
        String rsJson = "";
        try {
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherBody = new StringBuffer();
            strSqlVoucherBody.append("SELECT ROWNUM as FLOWID,T.* FROM (");
            strSqlVoucherBody.append("SELECT SAMEVOUCHER,PK_CORP,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,");
            strSqlVoucherBody.append("NCUSER,NVL(ISREAD,'U') ISREAD,NVL(ISSUCCESS,'U') ISSUCCESS,wb, ");
            strSqlVoucherBody.append("zy,kmdm,fzbz,dwdm,jtnwfz,'CNY' bz,j debitamount,d creditamount,REMARK ");
            strSqlVoucherBody.append("FROM CW_XZD_YSPZ_EXCHANGE WHERE SAMEVOUCHER = ? ");
            strSqlVoucherBody.append("ORDER BY DECODE(J,0,2,1),KMDM,DWDM");
            strSqlVoucherBody.append(") T");
            pstmtVoucherbody = connERP.prepareStatement(strSqlVoucherBody.toString());
            pstmtVoucherbody.setString(1, VoucherId);
            rsVoucherBody = pstmtVoucherbody.executeQuery();
            rsJson = DBUtils.extractJSONArray(rsVoucherBody).toString();
            System.out.println(rsJson);
        } catch (Exception ex) {
            ex.printStackTrace();
            rsJson = "失败";
        } finally {
            DBUtils.close(connERP);
            return rsJson;
        }

    }

    public static String VerifyVoucher(String VoucherId) {
        Connection connERP = null;
        Connection connFIN = null;
        PreparedStatement pstmtVoucherbody = null;
        PreparedStatement pstmtFinData = null;
        ResultSet rsVoucherBody = null;
        String rsJson = "";
        String ErrMsg = "";
        String retStatus = "Y";
        try {
            connERP = DBUtils.getConnection("ERP");
            StringBuffer strSqlVoucherBody = new StringBuffer();
            strSqlVoucherBody.append("SELECT ROWNUM as FLOWID,T.* FROM (");
            strSqlVoucherBody.append("SELECT SAMEVOUCHER,PK_CORP,to_char(rq,'YYYY-MM-DD') voucherdate,to_char(rq,'YYYY') fiscal_year,to_char(rq,'MM') accounting_period,");
            strSqlVoucherBody.append("NCUSER,NVL(ISREAD,'U') ISREAD,NVL(ISSUCCESS,'U') ISSUCCESS,wb, ");
            strSqlVoucherBody.append("zy,kmdm,fzbz,dwdm,jtnwfz,'CNY' bz,j debitamount,d creditamount,REMARK ");
            strSqlVoucherBody.append("FROM CW_XZD_YSPZ_EXCHANGE WHERE SAMEVOUCHER = ? ");
            strSqlVoucherBody.append("ORDER BY DECODE(J,0,2,1),KMDM,DWDM");
            strSqlVoucherBody.append(") T");
            pstmtVoucherbody = connERP.prepareStatement(strSqlVoucherBody.toString());
            pstmtVoucherbody.setString(1, VoucherId);
            rsVoucherBody = pstmtVoucherbody.executeQuery();
            List lstVoucherBody = DBUtils.ExtractData(rsVoucherBody);
            //判断借贷方金额是否平衡
            Double CreditAmount = 0.00;
            Double DebitAmount = 0.00;
            int i = 0;
            for (i = 0; i < lstVoucherBody.size(); i++) {
                DebitAmount += (Double) ((Map) lstVoucherBody.get(i)).get("debitamount");
                CreditAmount += (Double) ((Map) lstVoucherBody.get(i)).get("creditamount");
            }
            if (DebitAmount != CreditAmount) {
                retStatus = "N";
                ErrMsg += "借贷方金额不平";
            }

            //判断科目代码是否符合条件

            for (i = 0; i < lstVoucherBody.size(); i++) {

            }
        } catch (Exception ex) {
            ex.printStackTrace();
            retStatus = "U";
        } finally {
            DBUtils.close(connERP);
            DBUtils.close(connFIN);
            return retStatus;
        }
    }

    public static Map<String,String> VerifyVoucher(String VoucherID,List VoucherBody) {
        MDC.put("VOUCHERID",VoucherID);
        StringBuffer VerifyMsg= new StringBuffer();
        String resVerify = "Y";
        Map<String,String> mapRet = null;
        try {
            mapRet = new HashMap<>();
            VerifyMsg.append("开始对单据进行校验------\n");
            if (VoucherBody.size() == 0) {
                VerifyMsg.append("校验凭证时发现凭证分录行为空\n");
                resVerify = "N";
            }

            //判断借贷方金额是否平衡
            BigDecimal CreditAmount = new BigDecimal(0.00);
            BigDecimal DebitAmount = new BigDecimal(0.00);
            int i = 0;
            for (i = 0; i < VoucherBody.size(); i++) {
                DebitAmount = DebitAmount.add(Arith.getBigDecimal(((Map) VoucherBody.get(i)).get("DEBITAMOUNT")));
                CreditAmount = CreditAmount.add(Arith.getBigDecimal(((Map) VoucherBody.get(i)).get("CREDITAMOUNT")));
            }

            if (DebitAmount.compareTo(CreditAmount) != 0) {
                resVerify = "N";
                VerifyMsg.append("借贷方金额不平\n");

            }

            //判断科目代码是否符合条件
            VerifyMsg.append("校验结束-------\n");
        }catch (Exception ex) {
            _log.error(ex.getStackTrace().toString());
            VerifyMsg.append("校验时出现异常，请查看日志\n");
            resVerify = "U";
        }finally {
            mapRet.put("RESULTS",resVerify);
            mapRet.put("MESSAGE",VerifyMsg.toString());
            return  mapRet;
        }


    }


}

